# -*- coding: utf-8 -*-
"""
Created on Tue Jul 23 15:39:08 2024
输出所有参数回测的结果

@author: jzz
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import scipy.stats as stats
import os
import database_new_student as database_new
import copy
from dateutil.relativedelta import relativedelta
import datetime
import statsmodels.api as sm
import warnings
from scipy.stats import ttest_ind
import sys

warnings.filterwarnings("ignore")
plt.style.use('seaborn-v0_8')  # plt.style.use('dark_background') 
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签

def get_stock_score_pos(df_percent,a1,a2):#计算正向线性仓位
    score_stocks= df_percent.copy()
    length=len(df_percent)
    for i in range(0, length):
        x=df_percent.iloc[i,0]
        if x<a1:
            score_stock=0
        elif x<a2:
            score_stock=1/(a2-a1)*(x-a1)
        else:
            score_stock=1
        score_stocks.iloc[i,0] = score_stock
    return score_stocks  


def get_stock_score_neg(df_percent,a1,a2):#计算负向线性仓位
    score_stocks= df_percent.copy()
    length=len(df_percent)
    for i in range(0, length):
        x=df_percent.iloc[i,0]
        if x<a1:
            score_stock=1
        elif x<a2:
            score_stock=1/(a1-a2)*(x-a2)
        else:
            score_stock=0
        score_stocks.iloc[i,0] = score_stock
    return score_stocks  

def get_stock_score(df_percent,a1,a2,direction='正'):
    if direction=='正':
        return get_stock_score_pos(df_percent,a1,a2)
    elif direction=='负':
        return get_stock_score_neg(df_percent,a1,a2)
    
def hpfilter(table,lamb=5):#滤波函数
        '''
        获取每列的hp滤波后的趋势项
        table:pd.DataFrame或pd.Series
        
        '''
        is_series = isinstance(table, pd.Series)
        if is_series:
            table = pd.DataFrame(table)
            
        result = pd.DataFrame(index=table.index,columns=table.columns)
        for i in range(result.shape[1]):
            not_nan_data = table.iloc[:,i].dropna() 
            if len(not_nan_data)<2: # 至少要三个数才能做HP滤波
                continue 
            _,not_nan_data_hp = sm.tsa.filters.hpfilter(not_nan_data,lamb) 
            # 即使数据是间隔为nan也可处理，但有间隔nan如果太多，使用同一lambda可能会有误差
            result.iloc[:,i].loc[not_nan_data_hp.index] = not_nan_data_hp 
        
        if is_series:
            result = result.iloc[:,0] 
            
        return result.astype('float32') 

class Analyze:
    '''计算指标或画图进行分析''' 

    def evaluate_indicator(net_value,tradeday_per_year,anual_riskfree_rate):
        '''
        已知净值曲线之后进行各类指标的计算
    
        Parameters
        ----------
        net_value : pd.Series 
            时间序列.
        anual_riskfree_rate : float
            年化无风险利率.
        tradeday_per_year：如果净值序列是月度的，那就取12；如果是日度的，就取250（每年的交易日数据）；如果是周度的，就取52（每年的周数）
    
        Returns
        -------
        annual_yield：年化收益率
        annual_std：每期收益率的年化标准差
        sharpe_ratio：年化收益率的夏普比率
        drawdown：回撤曲线
        max_drawdown：最大回撤
    
        '''
        net_value0 = copy.copy(net_value)
        
        net_value = np.array(net_value)
        len_nav = len(net_value)
        annual_yield = (net_value[-1]/net_value[0])**(tradeday_per_year/len_nav)-1
        
        yield_of_nav = net_value[1:]/net_value[0:-1]-1
        annual_std = np.std(yield_of_nav)*np.sqrt(tradeday_per_year)
        
        sharpe_ratio = (annual_yield-anual_riskfree_rate)/annual_std
        
        max_nv = net_value0.cummax() 
        drawdown = (net_value0 - max_nv) / max_nv
        max_drawdown = np.max(-drawdown)
        
        # 分年度收益
        yield_year_last = net_value0.resample('y').last().pct_change() 
        yield_year_first_to_last = net_value0.resample('y').last()/net_value0.resample('y').first()-1
        
        return annual_yield,annual_std,sharpe_ratio,drawdown,max_drawdown,yield_year_last,yield_year_first_to_last 
    
    def evaluate_indicator_multi_asset(net_value,tradeday_per_year,anual_riskfree_rate):
        '''
        和evaluate_indicator区别是这里net_value是多资产的净值序列组成的dataframe

        Parameters
        ----------
        net_value : DataFrame
            DESCRIPTION.
        tradeday_per_year：如果净值序列是月度的，那就取12；如果是日度的，就取250（每年的交易日数据）；如果是周度的，就取52（每年的周数）
        anual_riskfree_rate : float
            0 or 0.03 ...

        Returns
        -------
        indicators : DataFrame
            DESCRIPTION.
        drawdowns : DataFrame
            DESCRIPTION.
        yield_year_lasts : DataFrame
            DESCRIPTION.
        yield_year_first_to_lasts : DataFrame
            DESCRIPTION.

        ''' 
        indicators = pd.DataFrame(index=net_value.columns,columns=['年化收益率','年化波动率','夏普比率','最大回撤'])
        drawdowns = [] 
        yield_year_lasts = [] 
        yield_year_first_to_lasts = [] 
        
        for code in net_value.columns:
            cur_nv = net_value[code]
            annual_yield,annual_std,sharpe_ratio,drawdown,max_drawdown,yield_year_last,yield_year_first_to_last = \
                Analyze.evaluate_indicator(cur_nv,tradeday_per_year,anual_riskfree_rate) 
            indicators.loc[code,['年化收益率','年化波动率','夏普比率','最大回撤']] = [annual_yield,annual_std,sharpe_ratio,max_drawdown]
            
            drawdowns.append(drawdown) 
            yield_year_lasts.append(yield_year_last) 
            yield_year_first_to_lasts.append(yield_year_first_to_last) 
        
        drawdowns = pd.concat(drawdowns,axis=1); 
        yield_year_lasts = pd.concat(yield_year_lasts,axis=1)
        yield_year_first_to_lasts = pd.concat(yield_year_first_to_lasts,axis=1)
        
        return indicators,drawdowns,yield_year_lasts,yield_year_first_to_lasts

#回测
def backtest_stock(table,score):
    table = pd.concat([table,score],axis=1).sort_index().dropna()#拼接2个dataframe，按index排序，删除缺失值 
    table.columns = ['行业指数','万得全A','行业指数收益率','万得全A收益率','择时指标得分']
    table['是否实际持有'] = table['择时指标得分'].shift(1)
    table['策略收益率'] = table['是否实际持有']*table['行业指数收益率']+(1-table['是否实际持有'])*table['万得全A收益率']
    table.loc[table.index[0],'策略收益率'] = 0 
    
    table['策略相对万得全A超额收益率']=table['策略收益率']-table['万得全A收益率']
    table['策略相对行业指数超额收益率']=table['策略收益率']-table['行业指数收益率']
    table['策略净值'] = (1+table['策略收益率']).cumprod()  #累乘

    table['行业指数净值']=table['行业指数']/table.loc[table.index[0],'行业指数']
    table['策略相对行业指数净值']=table['策略净值']/table['行业指数净值']
    table['万得全A净值']=table['万得全A']/table.loc[table.index[0],'万得全A']
    table['策略相对万得全A净值']=table['策略净值']/table['万得全A净值']
    
    table['策略是否绝对获胜'] = (table['策略收益率']>0)
    table['策略是否相对万得全A获胜'] = (table['策略收益率']-table['万得全A收益率'])>0
    table['策略是否相对行业指数获胜'] = (table['策略收益率']-table['行业指数收益率'])>0
    absolute = table['策略是否绝对获胜'].sum()/table['策略是否绝对获胜'].count()
    relative_A = table['策略是否相对万得全A获胜'].sum()/table['策略是否相对万得全A获胜'].count()
    relative_industry = table['策略是否相对行业指数获胜'].sum()/table['策略是否相对行业指数获胜'].count()
    _,_, sharpe_ratio, _, max_drawdown, _, _ = Analyze.evaluate_indicator(table['策略净值'], 12, 0)
    len_nav = table.shape[0]
    annual_yield = (table['策略净值'].iloc[-1]/table['策略净值'].iloc[0])**(12/len_nav)-1
    annual_yieldindustry=(table['行业指数净值'].iloc[-1]/table['行业指数净值'].iloc[0])**(12/len_nav)-1
    annual_yieldA=(table['万得全A净值'].iloc[-1]/table['万得全A净值'].iloc[0])**(12/len_nav)-1
    annual_yield_industry=annual_yield-annual_yieldindustry
    annual_yield_A=annual_yield-annual_yieldA
    return table,annual_yield,annual_yield_A,annual_yield_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown

def get_percentile(windows, df, start_date, end_date):
    #计算历史分位数,输入窗口、数据、起始和结束时间
    df_percent = pd.DataFrame(np.nan,index=df.index,columns=df.columns)
    df_percent=df_percent.truncate(start_date,end_date)
    start_loc = df.index.get_loc(start_date)
    end_loc = df.index.get_loc(end_date)
    for i in range(start_loc, end_loc+1):
        df_before_i = df.iloc[:i+1]
        if len(df_before_i)>=windows:#历史数据超过windows
            pre = df.iloc[i-windows+1:i+1,0]
            df_percent.iloc[i-start_loc]=stats.percentileofscore(pre, df.iloc[i,0])/100
        elif len(df_before_i)>=windows_min:#历史数据超过windows_min但不到windows
            pre = df.iloc[0:i+1,0]
            df_percent.iloc[i-start_loc]= stats.percentileofscore(pre, df.iloc[i,0])/100
        else:
            df_percent.iloc[i-start_loc]=np.nan
    return df_percent

class Fs:
    '''用于财报的工具函数'''
    
    def calc_TTM(table):
        '''计算TTM，输入的必须为标准化后的财务表格 
           要求输入：table的columns为股票代码,index为datetimeIndex'''
        dates = pd.date_range(table.index[0],table.index[-1],freq='q') 
        table = table.reindex(dates) # 将表格标准化成index为每个季度
        
        table_ttm = pd.DataFrame(index=table.index,columns=table.columns)
        for i in range(3,table.shape[0]):
            cur_date = table.index[i]
            if cur_date.month==12:
                table_ttm.iloc[i,:] = copy.copy(table.iloc[i,:])
            elif cur_date.month==3:
                table_ttm.iloc[i,:] = copy.copy(table.iloc[i,:]+(table.iloc[i-1,:]-table.iloc[i-4,:]))
            elif cur_date.month==6:
                table_ttm.iloc[i,:] = copy.copy(table.iloc[i,:]+(table.iloc[i-2,:]-table.iloc[i-4,:]))
            elif cur_date.month==9:
                table_ttm.iloc[i,:] = copy.copy(table.iloc[i,:]+(table.iloc[i-3,:]-table.iloc[i-4,:]))
        return table_ttm 
    
    def calc_single_quarter(table):
        '''计算单季度指标，输入的必须为标准化后的财务表格
        要求输入：columns为股票代码
        index为datetime'''
        dates = pd.date_range(table.index[0],table.index[-1],freq='q') 
        table = table.reindex(dates) # 将表格标准化成index为每个季度
        
        table_single = pd.DataFrame(index=table.index,columns=table.columns)
        for i in range(1,table.shape[0]):
            cur_date = table.index[i]
            if cur_date.month==3:
                table_single.iloc[i,:] = copy.copy(table.iloc[i,:])
            else: 
                table_single.iloc[i,:] = copy.copy(table.iloc[i,:]-table.iloc[i-1,:])
        return table_single 
    
    def calc_avg(table,window=5,min_periods=1):
        '''计算存量的window期均值
        如果为nan，则该期不计入平均
        要求输入：columns为股票代码
        index为datetime'''    
        
        dates = pd.date_range(table.index[0],table.index[-1],freq='q') 
        table = table.reindex(dates) # 将表格标准化成index为每个季度
        
        # 注意这里的min_periods=1可以使rolling函数只取一个数据点来做平均，窗口内至少有一个数据点即可
        table_avg = table.rolling(window,min_periods=min_periods).mean()
        return table_avg 

    def to_month_data(df):
        '''将财报数据reindex成均匀的月频''' 
        new_index = pd.date_range(df.index[0],df.index[-1],freq='m') 
        df = df.reindex(new_index) 
        return df 
    
    def calc_yoy(df):
        '''计算同比''' 
        df2 = Fs.to_month_data(df) 
        df_yoy = df2/df2.shift(12)-1 
        return df_yoy 

    def get_next_n_month_last_date(cur_date,n):
        '''获取当天的下n个月的最后一天'''
        next_month = cur_date + relativedelta(months=n+1) 
        next_month = next_month.replace(day=1) 
        next_month_last_date = next_month-datetime.timedelta(days=1)
        return next_month_last_date 

    def deal_inf(df):
        '''处理inf或者-inf值，将其替换成np.nan''' 
        df = df.replace({np.inf:np.nan,-np.inf:np.nan}) 
        return df 


#查找最优值，输入table：是测试部分合并好的矩阵，flag：原指标/一阶差分
def grid_search_dim2(table,df_percent_dict,df_diff_percent_dict,df_filter_percent_dict,df_filter_diff_percent_dict):
    # 创建空的DataFrame
    columns = ['形式', '方向','是否带滤波','窗口','参数1','参数2','绝对收益年化', 
               '相对万得全A超额收益年化','相对行业指数超额收益年化','绝对胜率',
               '相对万得全A胜率','相对行业指数胜率','夏普比率','最大回撤']
    data_new = pd.DataFrame(columns=columns)
    for windows in windows_year:
        print(windows)
        direction='正'
        for x1 in np.arange(0.25,1,0.25):
            for x2 in np.arange(max(x1,0.5),1,0.25):
                
                df_percent=df_percent_dict[windows]
                score=get_stock_score(df_percent,x1,x2,direction)
                _,returnrate,returnrate_A,returnrate_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown=backtest_stock(table,score)
                new_row = ['原指标', direction,'否', windows,x1,x2,returnrate,returnrate_A,returnrate_industry,absolute, relative_A,relative_industry,sharpe_ratio,max_drawdown]
                data_new.loc[len(data_new)] = new_row
                
                df_percent=df_diff_percent_dict[windows]
                score=get_stock_score(df_percent,x1,x2,direction)
                _,returnrate,returnrate_A,returnrate_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown=backtest_stock(table,score)
                new_row = ['一阶差分', direction,'否', windows,x1,x2,returnrate,returnrate_A,returnrate_industry,absolute, relative_A,relative_industry,sharpe_ratio,max_drawdown]
                data_new.loc[len(data_new)] = new_row
                
                df_percent=df_filter_percent_dict[windows] 
                score=get_stock_score(df_percent,x1,x2,direction)
                _,returnrate,returnrate_A,returnrate_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown=backtest_stock(table,score)
                new_row = ['原指标', direction, '是', windows,x1,x2,returnrate,returnrate_A,returnrate_industry,absolute, relative_A,relative_industry,sharpe_ratio,max_drawdown]
                data_new.loc[len(data_new)] = new_row
                
                df_percent=df_filter_diff_percent_dict[windows]
                score=get_stock_score(df_percent,x1,x2,direction)
                _,returnrate,returnrate_A,returnrate_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown=backtest_stock(table,score)
                new_row = ['一阶差分', direction,'是', windows,x1,x2,returnrate,returnrate_A,returnrate_industry,absolute, relative_A,relative_industry,sharpe_ratio,max_drawdown]
                data_new.loc[len(data_new)] = new_row
                
        direction='负'
        for x1 in np.arange(0.25,0.6,0.25):
            for x2 in np.arange(x1,1,0.25):
                
                df_percent=df_percent_dict[windows]
                score=get_stock_score(df_percent,x1,x2,direction)
                _,returnrate,returnrate_A,returnrate_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown=backtest_stock(table,score)
                new_row = ['原指标', direction, '否', windows,x1,x2,returnrate,returnrate_A,returnrate_industry,absolute, relative_A,relative_industry,sharpe_ratio,max_drawdown]
                data_new.loc[len(data_new)] = new_row
                
                df_percent=df_diff_percent_dict[windows]
                score=get_stock_score(df_percent,x1,x2,direction)
                _,returnrate,returnrate_A,returnrate_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown=backtest_stock(table,score)
                new_row = ['一阶差分', direction,'否', windows,x1,x2,returnrate,returnrate_A,returnrate_industry,absolute, relative_A,relative_industry,sharpe_ratio,max_drawdown]
                data_new.loc[len(data_new)] = new_row
                
                df_percent=df_filter_percent_dict[windows] 
                score=get_stock_score(df_percent,x1,x2,direction)
                _,returnrate,returnrate_A,returnrate_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown=backtest_stock(table,score)
                new_row = ['原指标', direction, '是', windows,x1,x2,returnrate,returnrate_A,returnrate_industry,absolute, relative_A,relative_industry,sharpe_ratio,max_drawdown]
                data_new.loc[len(data_new)] = new_row
                
                df_percent=df_filter_diff_percent_dict[windows]
                score=get_stock_score(df_percent,x1,x2,direction)
                _,returnrate,returnrate_A,returnrate_industry,absolute, relative_A, relative_industry,sharpe_ratio, max_drawdown=backtest_stock(table,score)
                new_row = ['一阶差分', direction,'是', windows,x1,x2,returnrate,returnrate_A,returnrate_industry,absolute, relative_A,relative_industry,sharpe_ratio,max_drawdown]
                data_new.loc[len(data_new)] = new_row
                
    return data_new

def single_deal(data_xianxing,shift_num,need_yoy,is_w_d):
        '''对单指标数据进行滞后、同比和降频等处理
        is_w_d是指是否周、日频数据'''
        if is_w_d: # 原数据是周、日频，需要降成月频
            data_xianxing = data_xianxing.resample('m').mean() 
        else:
            data_xianxing = data_xianxing.resample('m').last()
        if need_yoy: # 原数据是当月值，需要处理成当月同比
            data_xianxing = Fs.calc_yoy(data_xianxing)
        data_xianxing0 = copy.copy(data_xianxing)
        last_date = data_xianxing.index[-1]
        # 为了防止滞后一期或两期时丢失最新数据，要做序列延伸 
        for i in range(shift_num):
            cur_date = last_date
            next_date = Fs.get_next_n_month_last_date(cur_date,i+1)
            data_xianxing.loc[next_date] = np.nan 
        data_xianxing = data_xianxing.shift(shift_num)  
        # 因为做了滞后处理，所以横坐标的日期对应的数据是当日能拿到的最新数据
        # 比如0531这天对应的数据就是0531时能拿到的最新数据
        # 这个数据可能是4月的（滞后一期），也可能是3月的（滞后两期），也可能是当月的（日、周频）
        # 如果我在0519跑程序，跑出来的是0531的数据是nan，说明5月份截至19日还没有数据出来（可能是4月的数据或3月的数据）
        # 如果我在0519跑程序，跑出来的是0531的数据不是nan，说明5月份的数据已经出来了（可能是4月的数据或3月的数据，也可能是周、日频的5月的最新数据）
        data_xianxing = Fs.deal_inf(data_xianxing) # 如果进行了yoy那有可能出现inf值
        data_xianxing0 = Fs.deal_inf(data_xianxing0)
        
        return data_xianxing,data_xianxing0 
    
def get_filter_percent(df, windows,start_date,end_date):
    df_filter_percent = pd.DataFrame(np.nan,index=df.index,columns=df.columns)
    df_filter_percent = df_filter_percent.truncate(start_date,end_date)
    
    df_filter_diff_percent = pd.DataFrame(np.nan,index=df.index,columns=df.columns)
    df_filter_diff_percent=df_filter_diff_percent.truncate(start_date,end_date)
    
    start_loc = df.index.get_loc(start_date)
    end_loc = df.index.get_loc(end_date) 
    
    for j in range(start_loc, end_loc+1):
        df_before_j = df.iloc[:j+1]
        if len(df_before_j)>=windows_hp: #历史数据超过windows_hp
            df_filter_j=hpfilter(df.iloc[j-windows_hp+1:j+1],5)
            all_values1 = df_filter_j.values.flatten()
            all_values1 = all_values1[-windows*12:]
            
            df_filter_diff_j=df_filter_j.diff()
            all_values2 = df_filter_diff_j.values.flatten()
            all_values2 = all_values2[-windows*12:]
            all_values2 = all_values2[1:]
            
            df_filter_percent.iloc[j-start_loc]=stats.percentileofscore(all_values1, all_values1[-1])/100
            df_filter_diff_percent.iloc[j-start_loc]= stats.percentileofscore(all_values2, all_values2[-1])/100
        
        elif len(df_before_j)>=windows_min:#历史数据超过windows_min但不到windows_max
            df_filter_j=hpfilter(df.iloc[:j+1,0],5)
            all_values1 = df_filter_j.values.flatten()
            df_filter_diff_j=df_filter_j.diff()
            all_values2 = df_filter_diff_j.values.flatten()
            all_values2 = all_values2[1:]
            df_filter_percent.iloc[j-start_loc] = stats.percentileofscore(all_values1, all_values1[-1])/100
            df_filter_diff_percent.iloc[j-start_loc]= stats.percentileofscore(all_values2, all_values2[-1])/100
        else:
            df_filter_percent.iloc[j-start_loc]= np.nan
            df_filter_diff_percent.iloc[j-start_loc]= np.nan
    return df_filter_percent, df_filter_diff_percent

'''训练部分'''



#需要更改行业
#需要更改输入时间
#industry=str(sys.argv[1])
def main(industry='家用电器'):
    # industry='家用电器'
    
    #输入行业指数
    
    target1 = pd.read_excel('C:/Users/jzz/Desktop/申万一级行业指数行情''.xlsx',index_col=0)
    target1=target1[industry]
    target1=pd.DataFrame(target1)
    
    
    
    #根据不同的回测时间设定文件名
    if start_date1=='2015-12-31' and end_date1==end_date1:
        kk='全样本'
    elif start_date1=='2021-12-31' and end_date1==end_date1:
        kk='2021年后'
    #elif start_date1=='2015-12-31' and end_date1==end_date1:
       # kk='2021年前'
    
    
    
    
    #拼接行业指数和万得全A
    table_train = pd.concat([target1.resample('m').last(),
                             target2.resample('m').last()],
                            axis=1).sort_index().dropna()#拼接2个dataframe，改成月频数据，按index排序，删除缺失值 
    table_train.columns = ['行业指数','万得全A']
    table_train['行业指数收益率'] = table_train['行业指数'].pct_change()  
    table_train['万得全A收益率'] = table_train['万得全A'].pct_change()  
    table_train = table_train.truncate(start_date1,end_date1)
    
    #输入指标总体情况表格
    df_flag = pd.read_excel(folder+industry+'/'+industry+'指标信息.xlsx')
    num=df_flag.shape[0]#指标总数
    
    #创建最优参数表格
    columns1 = ['代码','名称','形式', '方向','是否带滤波','窗口','参数1','参数2']
    data_new1 = pd.DataFrame(columns=columns1)
    
    #调用数据库
    yunmacro = database_new.YunMacro()
    
    
    columns1 = ['指标代码', '名称','正向绝对收益年化', '负向绝对收益年化','绝对收益年化p值',
                '正向绝对胜率', '负向绝对胜率','绝对胜率p值']
    data_grouped1 = pd.DataFrame(columns=columns1)
    
    for i in range(num):
        #数据预处理
        code= df_flag['代码'].iloc[i]
        sql = "select end_dt, imvalue from macro_data where imcode='{}'".format(code)
        #sql = "select end_dt, imvalue from macro_data where imcode='M009042861'"
        data = yunmacro.read_sql(sql)
        data['end_dt'] = pd.to_datetime(data['end_dt'])  # 转换为日期类型
        data.set_index('end_dt', inplace=True)
        shift_num=df_flag['滞后期数'].iloc[i]
        need_yoy=df_flag['是否需同比操作'].iloc[i]
        is_w_d=df_flag['是否需要降频'].iloc[i]
        name=df_flag['指标名称'].iloc[i]
        name = name.replace(":", "")
        name = name.replace("/", "")
        name = name.replace(">", "大于")
        name = name.replace("<", "小于")
        df,_=single_deal(data,shift_num,need_yoy,is_w_d)
        df = df.ffill() #前向填充
        #获取指标的时间长度
        first_date=df.index[0]
        last_date=df.index[-1]
        #如果指标范围超过设定的回测时间段，那没问题；如果不到就以指标的时间段来
        if first_date <= pd.Timestamp(start_date1):
            start_date = start_date1
        else:
            start_date = first_date
        if last_date == pd.Timestamp(end_date1):
            end_date = end_date1
        else:
            end_date = last_date
        df_diff = df.diff()
        #计算df和df_diff的分位数并保存，在字典里储存分位数dataframe
        df_percent_dict={}
        df_diff_percent_dict={}
        df_filter_percent_dict={}
        df_filter_diff_percent_dict={}
        for windows in windows_year:
            df_percent_dict[windows]= get_percentile(windows*12,df,start_date,end_date)
            df_diff_percent_dict[windows]= get_percentile(windows*12,df_diff,start_date,end_date)
        for windows in windows_year:
        #计算滤波结果分位数
            df_filter_percent,df_filter_diff_percent=get_filter_percent(df,windows,start_date,end_date)
            df_filter_percent_dict[(int(windows))]=df_filter_percent
            df_filter_diff_percent_dict[(int(windows))]=df_filter_diff_percent
            
        #表格处理
        new_data=grid_search_dim2(table_train,df_percent_dict,df_diff_percent_dict,df_filter_percent_dict,df_filter_diff_percent_dict)
        new_data.insert(0, '指标代码', code)
        new_data.insert(0, '指标名称', name)
        new_data.sort_values(by='绝对收益年化', inplace=True,ascending=False,axis=0)
        
        print('wind代码：',code)
    
        data1 = pd.DataFrame(new_data)
        folder_out=folder+industry+'/'+industry+kk+'/'#输出的文件夹路径
    
        # 创建与文件名相同的文件夹
        folder_path = folder_out+code+name
        os.makedirs(folder_path, exist_ok=True)
    
        # 将DataFrame保存为Excel文件
        excel_file_path1 = folder_out+code+name+'/参数及结果''.xlsx'
        data1.to_excel(excel_file_path1, index=False)
    
#%% 主程序
#更改start_date可以更改回测时间
start_date1 = '2015-12-31'
end_date1='2024-7-31'
folder = 'C:/Users/jzz/Desktop/全行业/'
#输入窗口范围
windows_year = [1,3,5] 
windows_min = 12*min(windows_year)
windows_hp = 12*5

#输入万得全A
yunwind = database_new.YunWind()
sql2 = "Select TRADE_DT,S_DQ_CLOSE from AINDEXWINDINDUSTRIESEOD where S_INFO_WINDCODE='881001.WI'"
target2 = yunwind.read_sql(sql2)
target2['TRADE_DT'] = pd.to_datetime(target2['TRADE_DT'])  # 转换为日期类型
target2.set_index('TRADE_DT', inplace=True)

#
                            
industry_values = ['通信','房地产','公用事业',
              '家用电器','建筑材料','国防军工','传媒','纺织服饰',
              '机械设备','有色金属','基础化工','钢铁',
              '电力设备','电子','农林牧渔','建筑装饰',
              '煤炭','轻工制造','医药生物',
              '计算机','交通运输','美容护理','汽车',
              '商贸零售','食品饮料','社会服务','石油石化']



# 循环运行 my_script.py，并传递不同的 a 值
for i in range(len(industry_values)):
    # if i<8:
    #     continue
    industry = industry_values[i]
    main(industry)
    print(industry)


